package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.GUID;
import edu.byu.intex2.data.bo.Part;
import edu.byu.intex2.data.bo.PartServiceLine;
import edu.byu.intex2.data.bo.ServiceLine;
import java.sql.*;

/**
 * ServiceLine Data Access Object
 * @author Brady
 */
public class ServiceLineDAO {

    /////////////////////////////////////////////
    ///   Singleton code
    private static ServiceLineDAO instance = null;

    /** Creates a new instance of ServiceLineDAO */
    private ServiceLineDAO() {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized ServiceLineDAO getInstance() {
        if (instance == null) {
            instance = new ServiceLineDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /**
     * Creates a new serviceLine in the database
     * @param id GUID
     * @param taskId id of task
     */
    public ServiceLine create(String serviceId, String taskId) throws DataException {
        ServiceLine serviceLine = new ServiceLine(serviceId + taskId);
        serviceLine.setObjectAlreadyInDB(false);

        // Set Service ID
        serviceLine.setServiceId(serviceId);

        // Set Task Id
        serviceLine.setTaskId(taskId);

        Cache.getInstance().put(serviceLine.getId(), serviceLine); // put in cache just in case...
        return serviceLine;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /**
     * Reads an existing serviceLine from the database     */
    public ServiceLine read(String serviceId, String taskId) throws DataException {
        // Check Cache -- can't with an serviceLine
        String id = serviceId + taskId;
        if (Cache.getInstance().containsKey(id)) {
            return (ServiceLine) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try {
            return read(serviceId, taskId, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the ServiceLine object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /**
     * Internal method to read an existing serviceLine from the database
     * @return serviceLine
     */
    synchronized ServiceLine read(String serviceId, String taskId, Connection conn) throws Exception {
        // Check Cache
        String id = serviceId + taskId;
        if (Cache.getInstance().containsKey(id)) {
            return (ServiceLine) Cache.getInstance().get(id);
        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM service_line where service_id=? AND task_id=?");

        try {
            stmt.setString(1, serviceId);
            stmt.setString(2, taskId);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next()) {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("ServiceLine with serviceId '" + serviceId + "' and taskId '" + taskId + "' not found.");
        } finally {
            stmt.close();
        }
    }//read

    /** Internal method to create a serviceLine object from a record  */
    synchronized ServiceLine readRecord(ResultSet rs, Connection conn) throws Exception {
        String id = rs.getString("service_id") + rs.getString("task_id");
        if (Cache.getInstance().containsKey(id)) {
            return (ServiceLine) Cache.getInstance().get(id);
        }
        // Create new serviceLine object
        ServiceLine serviceLine = new ServiceLine(GUID.generate()); // generate a new local GUID.  It won't be saved to the DB

        // ServiceLine is already in DB
        serviceLine.setObjectAlreadyInDB(true);

        // Put serviceLine in cache
        Cache.getInstance().put(serviceLine.getId(), serviceLine);

        // Set ServiceLine Attributes
        serviceLine.setServiceId(rs.getString("service_id"));
        serviceLine.setTaskId(rs.getString("task_id"));
        serviceLine.setCostOfLabor(rs.getInt("labor_cost"));
        serviceLine.setCostOfParts(rs.getInt("parts_cost"));

        // ServiceLine is dirty and needs to be saved to DB
        serviceLine.setDirty(false);

        return serviceLine;
    }//readRecord


    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing serviceLine in the database */
    public void save(ServiceLine serviceLine) throws DataException {
        // get a connection from the pool, save all of the services first using the other serviceLine method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try {
            save(serviceLine, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e) {
            try {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the serviceLine database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the serviceLine object information.", e);
        } finally {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a serviceLine in the database */
    void save(ServiceLine serviceLine, Connection conn) throws Exception {
        // put the serviceLine object to cache
        Cache.getInstance().put(serviceLine.getId(), serviceLine);

        // decide whether to update or insert into the database
        if (serviceLine.isObjectAlreadyInDB() && serviceLine.isDirty()) {
            update(serviceLine, conn);
        } else if (serviceLine.isDirty()) {
            insert(serviceLine, conn);
        }//if
    }

    /** External method to update or create a serviceLine in the database without committing connection */
    public void saveNoCommit(ServiceLine serviceLine, Connection conn) throws Exception {
        // Get Already in Databsae
         Boolean alreadyInDB = serviceLine.isObjectAlreadyInDB();

        // decide whether to update or insert into the database
        if (alreadyInDB) {
            update(serviceLine, conn);
        } else {
            insert(serviceLine, conn);
        }


         // Get the PartServiceLine for every part to save to the database
         for (PartServiceLine psl : serviceLine.getPslList()){
            // Save No Commit our pretty task PSL (w00t!)
            PartServiceLineDAO.getInstance().saveNoCommit(psl, conn);
         }
    }

    /** Saves an existing serviceLine to the database */
    private void update(ServiceLine serviceLine, Connection conn) throws Exception {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE service_line SET labor_cost=?, parts_cost=? WHERE service_id=? AND task_id=?");
        try {
            // Get ServiceLine Attributes and add them to the statement
            stmt.setDouble(1, serviceLine.getCostOfLabor());
            stmt.setDouble(2, serviceLine.getCostOfParts());
            stmt.setString(3, serviceLine.getServiceId());
            stmt.setString(4, serviceLine.getTaskId());
            stmt.execute();

            // ServiceLine is clean!
            serviceLine.setDirty(false);
        } finally {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new serviceLine into the database */
    private void insert(ServiceLine serviceLine, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO service_line (service_id, task_id, labor_cost, parts_cost ) VALUES (?, ?, ?, ?)");
        try {
            // Set ServiceLine Attributes
            stmt.setString(1, serviceLine.getServiceId());
            stmt.setString(2, serviceLine.getTaskId());
            stmt.setDouble(3, serviceLine.getCostOfLabor());
            stmt.setDouble(4, serviceLine.getCostOfParts());
            stmt.execute();

            serviceLine.setObjectAlreadyInDB(true); // now in DB
            serviceLine.setDirty(false); // object is clean
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(ServiceLine serviceLine) throws DataException {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods

}
